﻿CREATE PROCEDURE [dbo].[UpsertProject]
@ProjectID INT, @Name NVARCHAR (50), @Description NVARCHAR (1024), @ManagerUserID INT, @EstCompletionDate DATETIME, @EstDuration DECIMAL (10, 2), @CreationDate DATETIME, @StatusID INT, @ProjectTypeID INT, @BilledAsOf DATETIME, @ActivelyUsed BIT, @RevenueTypeID INT, @ProjectCode NVARCHAR (50), @BillingFrequencyID INT, @OutputProjectID INT OUTPUT
AS
BEGIN

	IF (NOT EXISTS(SELECT * FROM [dbo].[Users] WHERE [UserID]=@ManagerUserID))
	BEGIN
		SET @ManagerUserID = 0
	END

	IF EXISTS(SELECT * FROM [dbo].[Projects] WHERE [ProjectID] = @ProjectID)
		BEGIN
			UPDATE [dbo].[Projects]
			   SET [Name] = @Name
				  ,[Description] = @Description
				  ,[ManagerUserID] = @ManagerUserID
				  ,[EstCompletionDate] = @EstCompletionDate
				  ,[EstDuration] = @EstDuration
				  ,[CreationDate] = @CreationDate
				  ,[StatusID] = @StatusID
				  ,[ProjectTypeID] = @ProjectTypeID
				  ,[BilledAsOf] = @BilledAsOf
				  ,[ActivelyUsed] = @ActivelyUsed
				  ,[RevenueTypeID] = @RevenueTypeID
				  ,[ProjectCode] = @ProjectCode
				  ,[BillingFrequencyID] = @BillingFrequencyID
			 WHERE ProjectID = @ProjectID
			 SELECT TOP 1 @OutputProjectID = [ProjectID] FROM [dbo].[Projects] WHERE [ProjectID] = @ProjectID
		END
	ELSE
		BEGIN
			INSERT INTO [dbo].[Projects]
					(
					    [Name]
					   ,[Description]
					   ,[ManagerUserID]
					   ,[EstCompletionDate]
					   ,[EstDuration]
					   ,[CreationDate]
					   ,[StatusID]
					   ,[ProjectTypeID]
					   ,[BilledAsOf]
					   ,[ActivelyUsed]
					   ,[RevenueTypeID]
					   ,[ProjectCode]
					   ,[BillingFrequencyID]
					)
				 VALUES
					(	
						@Name
					   ,@Description
					   ,@ManagerUserID
					   ,@EstCompletionDate
					   ,@EstDuration
					   ,@CreationDate
					   ,@StatusID
					   ,@ProjectTypeID
					   ,@BilledAsOf
					   ,@ActivelyUsed
					   ,@RevenueTypeID
					   ,@ProjectCode
					   ,@BillingFrequencyID
					   )
				SET @OutputProjectID = @@IDENTITY
				RETURN @@ROWCOUNT
		END		
END

